#!/usr/bin/env python3
# coding: utf-8
# vim: fdm=marker fmr={{{,}}} cms=#%s#

import os,sys
import mysql.connector

from conf import Conf

class App:
    def main(self):
        self.__run()

    def __run(self):
        try:
#            conn=mysql.connector.connect(host="localhost", port=3306,
#                    user="dbuser", password="dbuser", database="data",
#                    charset="utf8", autocommit="True",
#                    connection_timeout=10)
            conn=mysql.connector.connect(host=Conf.mysql.host,
                    port=Conf.mysql.port,
                    user=Conf.mysql.user, password=Conf.mysql.password,
                    database=Conf.mysql.database,
                    charset="utf8", autocommit="True",
                    connection_timeout=10)
        except Exception as e:
            print("db connect fail: %s" % e)
            return

        try:
            self.__add(conn)
        except Exception as e:
            print("add fail: %s" % e)
            return

        try:
            self.__fetch(conn)
        except Exception as e:
            print("fetch fail: %s" % e)
            return

        try:
            conn.close()
        except:
            pass

    def __add(self, conn):
        fh=open(os.path.dirname(sys.argv[0])+"/../test.jpg", "rb")
        picture=fh.read()
        fh.close()

        sql=("insert into test_users"
                " (id,update_ts,login,name,job,address,picture)"
                " values(0,sysdate(),%s,%s,%s,%s,%s)")

        try:
            cur=conn.cursor()
            params=("PY001@qq.com", "东风", 0, "魔都", picture)
            cur.execute(sql, params)
        except mysql.connector.IntegrityError as e: 
            print("Duplicate, skip: %s" % e)
        finally:
            cur.close()

        try:
            cur=conn.cursor()
            params=[
                ("PY002@qq.com", "东风2", 1, "魔都2", picture),
                ("PY010@qq.com", "东风10", 2, "魔都10", picture),
                ]
            cur.executemany(sql, params)
        except mysql.connector.IntegrityError as e: 
                print("Duplicate, skip: %s" % e)
        finally:
            cur.close()

    def __fetch(self, conn):
        sql=("select id,login,name,picture from test_users"
                " where job>%s")
        cur=conn.cursor()
        params=(0,)
        cur.execute(sql, params)
        for (id, login, name, picture) in cur:
            login=str(login, "utf8")
            name=str(name, "utf8")
            print("id:%d login:%s name:%s picture:%d" %
                    (id, login, name, len(picture)))
#            fh=open(login+".jpg", "wb")
#            fh.write(picture)
#            fh.close()
        cur.close()

if __name__ == "__main__":
    App().main()
